﻿using BW.Dto;
using BW.IBLL;
using BW.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using NPOI.SS.Formula.Functions;
using NPOI.XSSF.UserModel;
using System.ComponentModel;
//using Microsoft.AspNetCore.StaticFiles; //用于根据扩展名取文件格式mime

namespace BW.API.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class MemberController : ControllerBase, IMemberBLL
    {
        public MemberController(IMemberBLL memberBLL)
        {
            MemberBLL = memberBLL;
        }

        public IMemberBLL MemberBLL { get; }

        [HttpPost]
        public async Task<int> AddMember(Member member)
        {
            return await MemberBLL.AddMember(member);
        }

        [HttpGet]
        public MemberPageWhereDto GetMemberPageWhere(int index, int size)
        {
            return MemberBLL.GetMemberPageWhere(index, size);   
        }

        [HttpGet]
        public Member GetMemberSingle(int id)
        {
            return MemberBLL.GetMemberSingle(id);
        }

        [HttpDelete]
        public async Task<int> LogiceDeleteMember(int id)
        {
            return await MemberBLL.LogiceDeleteMember(id);
        }

        [HttpDelete]
        public async Task<int> LogiceRangeDeleteMember(string idstring)
        {
            return await MemberBLL.LogiceRangeDeleteMember(idstring);
        }

        [HttpPut]
        public async Task<int> UpdateMember(Member member)
        {
            return await MemberBLL.UpdateMember (member);
        }



        [HttpGet]
        public IActionResult ExPort()
        {
            //先查希望被导出到文件中的数据

            //使用工具创建Excel对象
            XSSFWorkbook wb = new XSSFWorkbook();
            //在Excel对象中创建表
            var sheet = wb.CreateSheet("会员表");
            //在表对象中创建行
            var row0 = sheet.CreateRow(0);  //数字表示要创建第几行
            //在行对象中创建单元格
            var cell0 = row0.CreateCell(0);
            //给单元格赋值
            cell0.SetCellValue("你好，今天天气晴");


            //把Excel对象变成文件并且下载
            //先把Excel对象变成byte[]字节数组,再把字符数组变在文件下载
            byte[] bytes;
            using (MemoryStream ms=new MemoryStream())
            {
                wb.Write(ms);
                bytes = ms.ToArray();
            }

            var mime = new Microsoft.AspNetCore.StaticFiles.FileExtensionContentTypeProvider().Mappings[".xlsx"]; //根据扩展名来获取文件格式字符串
            return File(bytes, mime, "今天很开心.xlsx");
        }


        [HttpGet]
        public IActionResult ExProtExcel()
        {
            //查数据
            var members = MemberBLL.GetMemberList();
            //使用工具组件NPOI,用他的XSSFWorkbook类
            XSSFWorkbook sheets = new XSSFWorkbook();  //创建一个Excel对象
            var sheet = sheets.CreateSheet("会员信息");

            var r = sheet.CreateRow(0);
            r.CreateCell(0).SetCellValue("编号");  //创建单元格并且赋值
            r.CreateCell(1).SetCellValue("会员名称");  //创建单元格并且赋值
            r.CreateCell(2).SetCellValue("会员编号");  //创建单元格并且赋值
            r.CreateCell(3).SetCellValue("会员头像");  //创建单元格并且赋值
            r.CreateCell(4).SetCellValue("年龄");  //创建单元格并且赋值
            r.CreateCell(6).SetCellValue("密码");  //创建单元格并且赋值
            r.CreateCell(7).SetCellValue("创建时间");  //创建单元格并且赋值
            r.CreateCell(8).SetCellValue("状态");  //创建单元格并且赋值
            r.CreateCell(9).SetCellValue("等级编号");  //创建单元格并且赋值
            r.CreateCell(10).SetCellValue("是否删除");  //创建单元格并且赋值
            r.CreateCell(11).SetCellValue("创建人编号");  //创建单元格并且赋值
            r.CreateCell(12).SetCellValue("修改人编号");  //创建单元格并且赋值
            r.CreateCell(13).SetCellValue("修改时间");  //创建单元格并且赋值
            r.CreateCell(15).SetCellValue("删除人编号");  //创建单元格并且赋值

            int num = 1;  //因为第0行，作为表头
            //遍历会员集合，创建多行多列
            foreach (var member in members)
            {
                var row = sheet.CreateRow(num);
                row.CreateCell(0).SetCellValue(member.Id);  //创建单元格并且赋值
                row.CreateCell(1).SetCellValue(member.Name);  //创建单元格并且赋值
                row.CreateCell(2).SetCellValue(member.Code);  //创建单元格并且赋值
                row.CreateCell(3).SetCellValue(member.Img);  //创建单元格并且赋值
                row.CreateCell(4).SetCellValue(member.Age);  //创建单元格并且赋值

                row.CreateCell(5).SetCellValue(member.Sex);  //创建单元格并且赋值

                row.CreateCell(6).SetCellValue(member.Password);  //创建单元格并且赋值
                row.CreateCell(7).SetCellValue(member.CreateDate.ToString("yyyy-MM-dd HH:mm:ss"));  //创建单元格并且赋值
                row.CreateCell(8).SetCellValue(member.Start);  //创建单元格并且赋值
                row.CreateCell(9).SetCellValue(member.LevelId);  //创建单元格并且赋值

                //row.CreateCell(9).SetCellValue(member.Sex);  //创建单元格并且赋值

                row.CreateCell(10).SetCellValue(member.IsDelete);  //创建单元格并且赋值
                row.CreateCell(11).SetCellValue(member.CreateUserId);  //创建单元格并且赋值
                row.CreateCell(12).SetCellValue(member.UpdateUserId);  //创建单元格并且赋值
                row.CreateCell(13).SetCellValue(member.UpdateDate == null ? "" : member.UpdateDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));  //创建单元格并且赋值

                row.CreateCell(14).SetCellValue(member.DeleteDate==null?"": member.DeleteDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));  //创建单元格并且赋值
                row.CreateCell(15).SetCellValue(member.DeleteUserId);  //创建单元格并且赋值
                
                row.CreateCell(16).SetCellValue(member.Sex==2?"男":"女");  //性别
                row.CreateCell(17).SetCellValue(member.Start==1?"正常":"冻结");  //创建单元格并且赋值



                //row.CreateCell(2).SetCellValue("hello");

                num++;
            }
            //把创建的Excel对象加工成byte[]
            MemoryStream ms = new MemoryStream();
            sheets.Write(ms);
            byte[] bytes = ms.ToArray();
            //return File(bytes,"指定文件格式","文件名");
            //通过文件扩展获取文件的格式.xlsx；MIME

            string mime = new Microsoft.AspNetCore.StaticFiles.FileExtensionContentTypeProvider().Mappings[".xlsx"];

            return File(bytes, mime, "会员信息.xlsx");
        }

        [HttpGet]
        public IQueryable<Member> GetMemberList()
        {
            return MemberBLL.GetMemberList();
        }

        /// <summary>
        /// 导入会员数据
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public IActionResult ImprpotExcel(IFormFile file)
        {
            MemoryStream memory = new MemoryStream();
            file.CopyTo(memory);
            memory.Position = 0;//获取或设置流中的当前位置;设置回到0的位置，后面操作内存流对象时就会从0的位置开始使用

            XSSFWorkbook wb = new XSSFWorkbook(memory); //Excel对象
            var sheet = wb.GetSheet("会员信息");

            //取最后一行下标数值
            int num = sheet.LastRowNum;


            //var value = row.GetCell(0).ToString();
            //var value1 = row.GetCell(1).ToString();
            //var value2 = row.GetCell(2).ToString();
            //var value3 = row.GetCell(3).ToString();

            List<Member> list = new List<Member>();
            for (int i = 1; i <= num; i++)
            {
                var row = sheet.GetRow(i);//取第0行
                Member member = new Member();
                member.Name = row.GetCell(1).ToString();
                member.Start = Convert.ToInt32(row.GetCell(8).ToString());
                member.DeleteDate = row.GetCell(14).ToString() == "" ? null : Convert.ToDateTime(row.GetCell(14).ToString());
                member.CreateDate = Convert.ToDateTime(row.GetCell(7).ToString());
                member.Code = row.GetCell(2).ToString();
                member.CreateUserId = Convert.ToInt32(row.GetCell(11).ToString());
                member.DeleteUserId = Convert.ToInt32(row.GetCell(15).ToString());
                member.Img = row.GetCell(3).ToString();
                member.IsDelete = row.GetCell(10).BooleanCellValue; //布尔类型
                member.LevelId = Convert.ToInt32(row.GetCell(9).ToString());
                member.Password = row.GetCell(6).ToString();
                member.Sex = Convert.ToInt32(row.GetCell(5).ToString());

                list.Add(member);
            }

            //调用BLL层的批量会员新增方法
            return Ok(MemberBLL.AddRangeMember(list).Result);
        }

        [HttpPost]
        public async Task<int> AddRangeMember(List<Member> members)
        {
            return await MemberBLL.AddRangeMember(members);
        }
    }
}
